IF OBJECT_ID('dbo.RptAwardingReport') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptAwardingReport
    IF OBJECT_ID('dbo.RptAwardingReport') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptAwardingReport >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptAwardingReport >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptAwardingReport
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 7/26/2007
--
-- Description: Source of Report, "AwardingReport"
--                  In Insight DB Reporting Services
--
-- Parameters:  
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptAwardingReport()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 6/19/07     KC        Created
-- 7/26/07     KC        Redid Naming Conventions, readied for QA
-- 7/30/07     KC        Added check for current BudgetDates, Added
--                       PostAwardManagerName
-- 8/2/2007    KC        QA Feedback and BudgetDate Fixes
-- 8/6/2007    KC        Added '0' for 'ALL' in Params
-- 8/8/07      KC        Made Security Inner Join
-- 8/17/07     KC        Security changed to Cached table, also 
--                            UDF.CancerRelatedYN is "Yes/No" field now
-- 9/7/07      KC        Changed to new source table(s)
-- 9/13/07     KC        Changed Date Search to ProjectStartDate from DueDate
--                       Removed Deliverables join.
-- 9/18/07     KC        Added @PreAwardManager as Param. Fixed SponsorAgreementID
-- 9/21/07     KC        Added @Unit
-- 9/25/07     KC        Used ID's for Inst,Dept,Unit
-- 10/1/07     KC        Fixed outdated function reference. fnGetMultiCriteria
-- 10/10/07    KC        Fix for ProjectFundNumber
-- 12/17/08    KC        New Security function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
--
-- =======================================================================
CREATE PROCEDURE dbo.RptAwardingReport
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @Institution                       VARCHAR(100) = NULL,
     @Department                        VARCHAR(100) = NULL,
     @Unit                              VARCHAR(100) = NULL,
     @PrincipalInvestigator             VARCHAR(100) = NULL,             
     @ProposalStatus                    VARCHAR(100) = NULL,
     @BudgetStatus                      VARCHAR(100) = NULL,
     @Sponsor                           VARCHAR(100) = NULL,
     @FundNumber                        VARCHAR(100) = NULL,
     @PreAwardManager                   VARCHAR(100) = NULL,
     @PostAwardManager                  VARCHAR(100) = NULL,
     @UsingHumans                       VARCHAR(3) = NULL,
     @UsingAnimals                      VARCHAR(3) = NULL,
     @UsingBioHazard                    VARCHAR(3) = NULL,
     @UsingStemCell                     VARCHAR(10) = NULL,
     @UsingDNA                          VARCHAR(3) = NULL,
     @AdvancedFundYN                    VARCHAR(3) = NULL,
     @CancerRelatedYN                   VARCHAR(3) = NULL,
     @CostShareType                     VARCHAR(30) = NULL,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error    INT,
               @RowCount INT
     
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @Unit IN ('ALL','','0') SET @Unit = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @ProposalStatus IN ('ALL','','0') SET @ProposalStatus = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
     IF @PreAwardManager IN ('ALL','','0') SET @PreAwardManager = NULL
     IF @PostAwardManager IN ('ALL','','0') SET @PostAwardManager = NULL
     IF @BudgetStatus IN ('ALL','','0') SET @BudgetStatus = NULL
     IF @FundNumber IN ('ALL','','0') SET @FundNumber = NULL
     
     IF @UsingHumans IN ('ALL','','0') SET @UsingHumans = NULL
     IF @UsingAnimals IN ('ALL','','0') SET @UsingAnimals = NULL
     IF @UsingBioHazard IN ('ALL','','0') SET @UsingBioHazard = NULL
     IF @UsingStemCell IN ('ALL','','0') SET @UsingStemCell = NULL
     IF @UsingDNA IN ('ALL','','0') SET @UsingDNA = NULL
     IF @AdvancedFundYN IN ('ALL','','0') SET @AdvancedFundYN = NULL
     IF @CancerRelatedYN IN ('ALL','','0') SET @CancerRelatedYN = NULL
     IF @CostShareType IN ('ALL','','0') SET @CostShareType = NULL
     
     -- Performance Issues, Get Secured AgreementIds into a temp table
     CREATE TABLE   #tt_Agreements  (AgreementId INT)
     
     INSERT    #tt_Agreements (AgreementId)
     SELECT    AgreementId
     FROM      dbo.fnGetSecurityAgreements(@DomainUserId) 

     -- Result Set
     SELECT    AI.FolderNumber AS ProposalNumber,        
               AI.PrincipalInvestigator AS PrincipalInvestigator,
               AI.ProposalTitle AS ProposalTitle, 
               AI.Institution AS Institution,
               AI.Department AS Department,
               AI.ChiefCode AS ChiefCode,
               AI.Unit AS UnitCode,
               AI.SponsorName AS SponsorName,        
               AI.SponsorAgreementID AS SponsorNumber,
               CONVERT(VARCHAR(10),AI.ProjectStartDate,101) AS ProjectStartDate,
               CONVERT(VARCHAR(10),AI.ProjectEndDate,101) AS ProjectEndDate,
               CONVERT(VARCHAR(10),BUDG.BudgetStartDate,101) AS BudgetStartDate,
               CONVERT(VARCHAR(10),BUDG.BudgetEndDate,101) AS BudgetEndDate,
               BUDG.BudgetViewType AS BudgetPeriodStatus,
               AI.PostAwardManagerCode AS PostAwardManager,
               ISNULL(AI.PostAwardManagerName,' Post Award Manager Not Assigned') AS PostAwardManagerName,
               AI.PreAwardManager AS PreAwardManager,
               ISNULL(AI.PreAwardManagerName,' Pre Award Manager Not Assigned') AS PreAwardManagerName,
               AI.ApprovalNumber AS ApprovalNumber,
               PROJ.ProjectDirectCost AS DirectCostforProjectPeriod,
               BUDG.BudgetDirectCost AS DirectCostforBudgetPeriod,
               PROJ.ProjectIndirectCost AS FAforProjectPeriod,
               BUDG.BudgetIndirectCost AS FAforBudgetPeriod,
               AI.ProposalStatus AS ProposalStatus,
               ISNULL(BUDG.FundNumber,AI.ProjectFundNumber) AS FundNumber,  
               AI.CancerRelatedYN AS CancerRelated,
               AI.BiohazardYN AS BioHazard,
               AI.EmbryonicStemCells AS EmbryonicStemCells,
               AI.StudyHumanYN AS StudyHuman,
               AI.StudyAnimalYN AS StudyAnimal,
               AI.AdvanceFundYN AS AdvanceFundYN,
               AI.RecombinantDNAYN AS RecombinantDNA,
               AI.InstrumentType AS ProposalType,
               AI.CostShareType AS CostShareType,
               AI.InfoEdPropNumber AS InfoEdPropNumber,
               AI.BackupAccountNumber AS BackupAccountNumber,
               AI.PrincipalInvestigatorId AS PrincipalInvestigatorId,
               AI.SponsorID AS SponsorId,
               AI.ResourceId AS AgrResourceId,
               AI.AgreementId AS AgreementId
     FROM      dbo.AgrAgreement_Info AI LEFT OUTER JOIN 

               -- ProjectDirectCosts have to be summed from the Budget tables
               -- Over the life of the Agreement (Project)
               dbo.vwAgreementProjectBudgets PROJ ON AI.AgreementId = PROJ.AgreementId LEFT OUTER JOIN

               -- We need only the latest BudgetYear and BudgetDates for each 
               -- FolderNumber within the search date params.
               dbo.fnGetPeriodBudget(@StartDate, @EndDate) BUDG ON AI.AgreementId = BUDG.AgreementId INNER JOIN
               
               -- Security Issues: Not all Users can see all Agreements!
               #tt_Agreements AUTH ON AI.AgreementId = AUTH.AgreementId
                     
     WHERE     (@StartDate IS NULL OR PROJ.ProjectStartDate BETWEEN @StartDate AND @EndDate) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution))) AND
               (@Department IS NULL OR AI.DepartmentId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Department))) AND
               (@Unit IS NULL OR AI.UnitId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Unit))) AND
               (@PrincipalInvestigator IS NULL OR AI.PrincipalInvestigatorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PrincipalInvestigator ))) AND
               (@Sponsor IS NULL OR AI.SponsorId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @Sponsor ))) AND
               (@ProposalStatus IS NULL OR AI.AgreementStatusId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @ProposalStatus ))) AND
               (@PreAwardManager IS NULL OR AI.PreAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PreAwardManager ))) AND
               (@PostAwardManager IS NULL OR AI.PostAwardManagerId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria( @PostAwardManager ))) AND
               (@FundNumber IS NULL OR BUDG.FundNumber IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @FundNumber ))) AND
               (@BudgetStatus IS NULL OR BUDG.BudgetViewType IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @BudgetStatus ))) AND
               
               (@UsingHumans  IS NULL OR AI.StudyHumanYN = @UsingHumans) AND
               (@UsingAnimals  IS NULL OR AI.StudyAnimalYN = @UsingAnimals) AND
               (@UsingBioHazard  IS NULL OR AI.BiohazardYN = @UsingBioHazard) AND
               (@UsingStemCell  IS NULL OR RTRIM(AI.EmbryonicStemCells) NOT IN (NULL,'')) AND -- @UsingStemCell) AND
               (@UsingDNA IS NULL OR AI.RecombinantDNAYN = @UsingDNA) AND   
               (@AdvancedFundYN  IS NULL OR AI.AdvanceFundYN = @AdvancedFundYN) AND
               (@CancerRelatedYN IS NULL OR AI.CancerRelatedYN = @CancerRelatedYN) AND
               (@CostShareType IS NULL OR AI.CostShareType = @CostShareType) 
               
      ORDER BY AI.SponsorName, AI.Institution, AI.Department

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     
     DROP TABLE #tt_Agreements
     
     IF @Error!=0
          RETURN -1
          
     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.RptAwardingReport') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptAwardingReport >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptAwardingReport >>>'
go

GRANT EXECUTE ON dbo.RptAwardingReport TO InsightReport,Insight
go